from datetime import datetime, timedelta

import clickhouse_connect


def import_csv_to_clickhouse(client, csv_path: str, data_date: str) -> None:
    """
    将CSV文件导入到ClickHouse的network_device_status表

    参数:
    csv_path (str): CSV文件路径
    data_date (str): 数据日期，格式为YYYY-MM-DD
    """
    # 构建SQL查询
    query = f"""
    INSERT INTO work.network_device_status
    SELECT 
        `网元IP`,
        `小区`,
        `OLTPON`,
        `SN`,
        `接收光功率(dBm)`,
        -- 处理 '-' 和空字符串
        CASE 
            WHEN `ONU测距(m)` = '-' OR `ONU测距(m)` = '' THEN NULL
            ELSE `ONU测距(m)`
        END AS `ONU测距(m)`,
        toDate('{data_date}') AS `数据日期`
    FROM file(
        '{csv_path}',
        'CSVWithNames'
    )
    SETTINGS format_csv_delimiter = ','
    """

    try:
        # 执行SQL查询
        result = client.command(query)
        print(f"数据导入成功，影响行数: {result.summary['written_rows']}")
    except Exception as e:
        print(f"数据导入失败: {str(e)}")


def 查看():
    client = clickhouse_connect.get_client(
        host='192.168.67.56',
        port=8123,
        username='default',
        password=''
    )
    query = f'''
SELECT `数据日期`,
       count() AS `数量`
FROM work.network_device_status
where `数据日期`>'2025-07-15'
GROUP BY `数据日期`
ORDER BY `数据日期` desc
'''
    try:
        # 执行SQL查询
        result = client.query(query)

        # 打印列名
        print("|".join(result.column_names))

        # 打印每行数据
        for row in result.result_rows:
            print("|".join(str(item) for item in row))

    except Exception as e:
        print(f"数据查询失败: {str(e)}")


def batch_import_csv_to_clickhouse(start_date, end_date):
    # 创建ClickHouse客户端连接
    client = clickhouse_connect.get_client(
        host='192.168.67.56',
        port=8123,
        username='default',
        password=''
    )

    start = datetime.strptime(start_date, "%Y-%m-%d")
    end = datetime.strptime(end_date, "%Y-%m-%d")
    current_date = start

    while current_date <= end:
        # 生成CSV路径（示例：/hddisk/data_backup/网管/2505/网管0504.csv）
        year_month = current_date.strftime("%y%m")  # 例如 "2505" 表示2025年5月
        day = current_date.strftime("%m%d")  # 例如 "0504" 表示5月4日
        csv_path = f"/hddisk/data_backup/网管/{year_month}/网管{day}.csv"
        data_date = current_date.strftime("%Y-%m-%d")
        print(csv_path)
        print(data_date)
        import_csv_to_clickhouse(client, csv_path, data_date)
        current_date += timedelta(days=1)  # 移动到下一天


if __name__ == "__main__":
    batch_import_csv_to_clickhouse("2025-07-29", "2025-07-31")
    查看()
